﻿USE [Spediz]
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shippings_Containers]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shippings]'))
ALTER TABLE [dbo].[Shippings] DROP CONSTRAINT [FK_Shippings_Containers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Shippings]') AND type in (N'U'))
DROP TABLE [dbo].[Shippings]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Containers]') AND type in (N'U'))
DROP TABLE [dbo].[Containers]
GO

CREATE TABLE [dbo].[Containers](
	[ContainerId] [int] IDENTITY(1,1) NOT NULL,
	[Sign] nchar(4) NOT NULL, 
	[Number] int NOT NULL, 
	[Checkdigit] int NOT NULL, 
	[Type] nvarchar(6) NOT NULL,
	CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED ([ContainerId] ASC)
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Shippings](
	[ShippingId] [int] IDENTITY(1,1) NOT NULL, 
	[ContainerId] int NOT NULL, 
	[Type] nchar(1) NOT NULL, 
	[Seal] int NOT NULL, 
	[Weight] int NOT NULL, 
	[ToPort] nvarchar(100) NULL, 
	[FromPort] nvarchar(100) NULL, 
	[Ship] nvarchar(50) NULL, 
	[Forwarder] nvarchar(100) NULL, 
	[BoardingDate] datetime NULL, 
	[LandingDate] datetime NULL,
	CONSTRAINT [PK_Shipping] PRIMARY KEY CLUSTERED ([ShippingId] ASC)
	WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Shippings]  WITH CHECK ADD  CONSTRAINT [FK_Shippings_Containers] FOREIGN KEY([ContainerId])
REFERENCES [dbo].[Containers] ([ContainerId])
GO

ALTER TABLE [dbo].[Shippings] CHECK CONSTRAINT [FK_Shippings_Containers]
GO

--DELETE ALL DATA
DELETE FROM [dbo].[Shippings]
GO
DELETE FROM [dbo].[Containers]
GO

--INSERT CONTAINER DATA
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('EMCU',925822,5,'hc40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('MEDU',900001,0,'box40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('MEDU',900014,6,'box40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('MEDU',748572,9,'box40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('MEDU',900002,5,'box40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('CTNU',100945,2,'box20') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('COBU',400013,3,'open40') 
GO
INSERT INTO [dbo].[Containers]([Sign], [Number], [Checkdigit], [Type])VALUES('TRLU',553943,5,'open20') 
GO

--INSERT SHIPPING DATA
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (1,'E',  257455,20000,'TUNISI',NULL,'TRINITA','2011-09-01 17:59:00',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (1,'I',257454,19500,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 6:30:00','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (1,'E',257453,21000,'BARI',NULL,'GENESY','2011-08-12 13:22:00',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (1,'I',257452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:11:00','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (2,'I',357454,19500,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 6:40:00','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (2,'E',357453,21000,'BARI',NULL,'GENESY','2011-08-12 13:32:10',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (2,'I',357452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:16:34','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (3,'I',457454,18000,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 6:45:00','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (3,'E',457453,21000,'BARI',NULL,'GENESY','2011-08-12 13:43:37',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (3,'I',457452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:23:15','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (4,'I',557454,18000,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 6:50:00','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (4,'E',557453,21000,'BARI',NULL,'GENESY','2011-08-12 13:55:33',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (4,'I',557452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:30:02','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (5,'I',657454,18500,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 7:00:10','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (5,'E',657453,21000,'BARI',NULL,'GENESY','2011-08-12 14:00:59',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (5,'I',657452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:33:49','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (6,'I',757454,19000,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 7:05:33','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (6,'E',757453,21000,'BARI',NULL,'GENESY','2011-08-12 14:07:19',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (6,'I',757452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:38:11','DotNetSpediz')
GO


INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (8,'I',957454,12000,NULL,'GENOVA','UNIT-TEST',NULL,'2011-08-28 7:20:39','DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (8,'E',957453,21000,'BARI',NULL,'GENESY','2011-08-12 14:19:22',NULL,'DotNetSpediz')
GO
INSERT INTO [dbo].[Shippings]([ContainerId], [Type], [Seal], [Weight], [ToPort], [FromPort], [Ship], [BoardingDate], [LandingDate], [Forwarder])VALUES (8,'I',957452,20000,NULL,'TUNISI','TRINITA',NULL,'2011-08-1 23:50:12','DotNetSpediz')
GO